package CollegeInformation;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public  class DataDao {
    //面向数据库的数据操作类
//    添加教师
    public void add(Employee o) {
        Connection conn=null;
        try{
            conn= DBUtil.connection();
            String sql;

            if(o.getType().equalsIgnoreCase("teacher")){
                Teacher teacher=(Teacher) o;
                sql="INSERT INTO Employee(id,name,sex,age,type,department,major,title) VALUES(?,?,?,?,?,?,?,?)";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,teacher.getId());
                preparedStatement.setString(2,teacher.getName());
                preparedStatement.setString(3,teacher.getSex());
                preparedStatement.setInt(4,teacher.getAge());
                preparedStatement.setString(5, teacher.getType());
                preparedStatement.setString(6,teacher.getDepartment());
                preparedStatement.setString(7,teacher.getMajor());
                preparedStatement.setString(8,teacher.getTitle());
                preparedStatement.execute();
            }
            if(o.getType().equalsIgnoreCase("labmember")) {
                LabMember labMember=(LabMember)o;
                sql="INSERT INTO Employee(id,name,sex,age,type,lab,job) VALUES(?,?,?,?,?,?,?)";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,labMember.getId());
                preparedStatement.setString(2,labMember.getName());
                preparedStatement.setString(3,labMember.getSex());
                preparedStatement.setInt(4,labMember.getAge());
                preparedStatement.setString(5, labMember.getType());
                preparedStatement.setString(6,labMember.getLab());
                preparedStatement.setString(7,labMember.getJob());
                preparedStatement.execute();
            }
            if(o.getType().equalsIgnoreCase("Officer")) {
               Officer officer=(Officer)o;
                sql="INSERT INTO Employee(id,name,sex,age,type,political,title) VALUES(?,?,?,?,?,?,?)";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,officer.getId());
                preparedStatement.setString(2,officer.getName());
                preparedStatement.setString(3,officer.getSex());
                preparedStatement.setInt(4,officer.getAge());
                preparedStatement.setString(5, officer.getType());
                preparedStatement.setString(6,officer.getPolitical());
                preparedStatement.setString(7, officer.getOtitle());
                preparedStatement.execute();
            }
            if(o.getType().equalsIgnoreCase("TOfficer")) {
                TOfficer tofficer=(TOfficer)o;
                sql="INSERT INTO Employee(id,name,sex,age,type,department,major,title,political) VALUES(?,?,?,?,?,?,?,?,?)";
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,tofficer.getId());
                preparedStatement.setString(2,tofficer.getName());
                preparedStatement.setString(3,tofficer.getSex());
                preparedStatement.setInt(4,tofficer.getAge());
                preparedStatement.setString(5, tofficer.getType());
                preparedStatement.setString(6,tofficer.getDepartment());
                preparedStatement.setString(7, tofficer.getMajor());
                preparedStatement.setString(8,tofficer.getTitle());
                preparedStatement.setString(9, tofficer.getTOpolitical());
                preparedStatement.execute();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
        if (conn!= null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        }
    }
//删除教师
    public boolean delete(String id) {
        Connection conn=null;
        try{
            conn= DBUtil.connection();
            String sql="DELETE FROM Employee WHERE id=?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,id);
            preparedStatement.execute();
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }
//    查询ID并返回是否存在这个教师
 public boolean searchByid(String id){
     Connection conn=null;
     try {
         conn= DBUtil.connection();
         String sql="SELECT * FROM Employee WHERE id='"+id+"'";
         Statement statement =conn.createStatement();
         ResultSet resultSet = statement.executeQuery(sql);
         if(!resultSet.next()){//如果为空
             return true;
         }else{
             return false;
         }

     }catch (Exception e){
         e.printStackTrace();
         return false;
     }
 }
// 查询ID 返回教师
    public Employee search(String id) {
        Connection conn=null;
        try{
            conn= DBUtil.connection();
            String sql="SELECT * FROM Employee WHERE id='"+id+"'";
            List<Employee> employeeList=new ArrayList<Employee>();
            Statement statement =conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                if(resultSet.getString("type").equalsIgnoreCase("teacher")){
                    Teacher teacher=new Teacher(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"));
                    employeeList.add(teacher);

                }
                if(resultSet.getString("type").equalsIgnoreCase("labmember")){
                    LabMember labMember=new LabMember(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("lab"),resultSet.getString("job"));
                    employeeList.add(labMember);

                }
                if(resultSet.getString("type").equalsIgnoreCase("officer")){
                    Officer officer=new Officer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("political"),resultSet.getString("title"));

                    employeeList.add(officer);
                }
                if(resultSet.getString("type").equalsIgnoreCase("tofficer")){
                    TOfficer tOfficer=new TOfficer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"),resultSet.getString("political"));
                    employeeList.add(tOfficer);

                }
            }
            return employeeList.get(0);
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }


    }
//修改教师信息
    public void modify(Employee employee) {
        Connection conn = null;

        try {
            conn = DBUtil.connection();
            String sql;
            if (employee.getType().equalsIgnoreCase("teacher")) {
                sql = "UPDATE Employee SET name=?,sex=?, age=?,department=?,major=?,title=? WHERE id='"+employee.getId()+"'";
                Teacher teacher=(Teacher) employee;
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,teacher.getName());
                preparedStatement.setString(2,teacher.getSex());
                preparedStatement.setInt(3,teacher.getAge());
                preparedStatement.setString(4,teacher.getDepartment());
                preparedStatement.setString(5,teacher.getMajor());
                preparedStatement.setString(6,teacher.getTitle());
                preparedStatement.executeUpdate();
            }
            if(employee.getType().equalsIgnoreCase("labmember")){
                sql = "UPDATE Employee SET name=?,sex=?, age=?,lab=?,job=? WHERE id='"+employee.getId()+"'";
                LabMember labMember=(LabMember)employee;
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,labMember.getName());
                preparedStatement.setString(2,labMember.getSex());
                preparedStatement.setInt(3,labMember.getAge());
                preparedStatement.setString(4,labMember.getLab());
                preparedStatement.setString(5,labMember.getJob());
                preparedStatement.executeUpdate();
            }
            if (employee.getType().equalsIgnoreCase("officer")){
                sql = "UPDATE Employee SET name=?,sex=?, age=?,political=?,title=? WHERE id='"+employee.getId()+"'";
                Officer officer=(Officer)employee;
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,officer.getName());
                preparedStatement.setString(2,officer.getSex());
                preparedStatement.setInt(3,officer.getAge());
                preparedStatement.setString(4,officer.getPolitical());
                preparedStatement.setString(5, officer.getOtitle());
                preparedStatement.executeUpdate();
            }
            if(employee.getType().equalsIgnoreCase("tofficer")){
                sql = "UPDATE Employee SET name=?,sex=?, age=?,department=?,major=?,title=?,political=? WHERE id='"+employee.getId()+"'";
                TOfficer tofficer=(TOfficer)employee;
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
                preparedStatement.setString(1,tofficer.getName());
                preparedStatement.setString(2,tofficer.getSex());
                preparedStatement.setInt(3,tofficer.getAge());
                preparedStatement.setString(4,tofficer.getDepartment());
                preparedStatement.setString(5, tofficer.getMajor());
                preparedStatement.setString(6,tofficer.getTitle());
                preparedStatement.setString(7, tofficer.getTOpolitical());
                preparedStatement.executeUpdate();
            }


        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
//查询所有教师信息
        public  List<Employee> displayall() {
        Connection conn=null;Statement statement=null;
        ResultSet resultSet=null;
            try {
            conn= DBUtil.connection();
            String sql="SELECT * FROM Employee";
            List<Employee> employeeList=new ArrayList<Employee>();
            statement =conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                if(resultSet.getString("type").equalsIgnoreCase("teacher")){
                    Teacher teacher=new Teacher(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"));
                    employeeList.add(teacher);

                }
                if(resultSet.getString("type").equalsIgnoreCase("labmember")){
                    LabMember labMember=new LabMember(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("lab"),resultSet.getString("job"));
                    employeeList.add(labMember);

                }
                if(resultSet.getString("type").equalsIgnoreCase("officer")){
                    Officer officer=new Officer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("political"),resultSet.getString("title"));

                    employeeList.add(officer);
                }
                if(resultSet.getString("type").equalsIgnoreCase("tofficer")){
                    TOfficer tOfficer=new TOfficer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"),resultSet.getString("political"));
                    employeeList.add(tOfficer);

                }
            }
            return employeeList;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
                if(resultSet!=null){try {
                    resultSet.close();}catch (SQLException e){}
                }
                if(statement!=null){try {
                    statement.close();}catch (SQLException e){}
                }
                if(conn!=null){try {
                    conn.close();}catch (SQLException e){}
                }
        }
    }
//统计教师信息
    public statisticResult statistic() {
        Connection conn=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            conn= DBUtil.connection();
            String sql="SELECT * FROM Employee";
            statisticResult result=new statisticResult();
            List<Employee> employeeList=new ArrayList<Employee>();
            statement =conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                if(resultSet.getString("type").equalsIgnoreCase("teacher")){
                    Teacher teacher=new Teacher(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"));
                    employeeList.add(teacher);
                    result.Tnum++;

                }
                if(resultSet.getString("type").equalsIgnoreCase("labmember")){
                    LabMember labMember=new LabMember(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("lab"),resultSet.getString("job"));
                    employeeList.add(labMember);
                    result.Lnum++;

                }
                if(resultSet.getString("type").equalsIgnoreCase("officer")){
                    Officer officer=new Officer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("political"),resultSet.getString("title"));
                    employeeList.add(officer);
                    result.Onum++;
                }
                if(resultSet.getString("type").equalsIgnoreCase("tofficer")){
                    TOfficer tOfficer=new TOfficer(resultSet.getString("id"),resultSet.getString("name"),resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("type"),resultSet.getString("department"),resultSet.getString("major"),resultSet.getString("title"),resultSet.getString("political"));
                    employeeList.add(tOfficer);
                    result.TOnum++;

                }
                if(resultSet.getString("sex").equals("男")){

                    result.malesum++;

                }
                if(resultSet.getString("sex").equals("女")){

                    result.femalesum++;

                }
            }
            result.sum=employeeList.size();
            return result;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {
            if(resultSet!=null){try {
                resultSet.close();}catch (SQLException e){}
            }
            if(statement!=null){try {
                statement.close();}catch (SQLException e){}
            }
            if(conn!=null){try {
                conn.close();}catch (SQLException e){}
            }


        }

    }



}
